﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using SqlServerDRControlPanel;
using System.Security;
using System.Security.Principal;

namespace AutomatedDatabaseDR
{
    
    public class ConfigItems
    {
        private string AdminGroup = ConfigurationManager.AppSettings["SecurityGroup"];


        private string m_ConfigSvr;
        public string ConfigServer
        {
            get { return m_ConfigSvr; }
            set { m_ConfigSvr = value; }
        }
        private string m_ConfigDB;
        public string ConfigDB
        {
            get { return m_ConfigDB; }
            set { m_ConfigDB = value; }
        }

        private string m_ConfigDatabaseSchemaName;
        private string m_ConfigMirrorServerSchemaName;

        #region Constructor
        public ConfigItems(string server, string database)
        {
            m_ConfigSvr = server;
            m_ConfigDB = database;
            m_ConfigDatabaseSchemaName = ConfigurationManager.AppSettings["ConfigDatabaseSchemaName"];
            m_ConfigMirrorServerSchemaName = ConfigurationManager.AppSettings["ConfigPartnerServer"];
        }
        public ConfigItems()
        {
            m_ConfigDatabaseSchemaName = ConfigurationManager.AppSettings["ConfigDatabaseSchemaName"];
            m_ConfigMirrorServerSchemaName = ConfigurationManager.AppSettings["ConfigPartnerServer"];

        }
        #endregion


        #region Public Methods


        public void InsertPostActions(string databaseName, string sqlQuery)
        {

            string sqlConnStr = ConfigDataConnectionString();
            string spName = string.Format("{0}.usp_InsPostAction", m_ConfigDatabaseSchemaName);
            SqlConnection dbConn = new SqlConnection(sqlConnStr);
            SqlCommand sc = new SqlCommand(spName, dbConn);
            sc.CommandType = CommandType.StoredProcedure;

            sc.Parameters.Add("@DatabaseName", SqlDbType.VarChar, 50);
            sc.Parameters["@DatabaseName"].Value = databaseName;

            sc.Parameters.Add("@SqlScript", SqlDbType.VarChar);
            sc.Parameters["@SqlScript"].Value = sqlQuery;

            try
            {
                dbConn.Open();
                sc.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }

        /// <summary>
        /// Method to delete items from the config database.
        /// </summary>
        /// <param name="databaseName"></param>
        public void DeleteConfigItems(string databaseName)
        {
            string sqlConnStr = ConfigDataConnectionString();
            string spName = string.Format("{0}.usp_DeleteConfigItem", m_ConfigDatabaseSchemaName);
            SqlConnection dbConn = new SqlConnection(sqlConnStr);
            SqlCommand sc = new SqlCommand(spName, dbConn);
            sc.CommandType = CommandType.StoredProcedure;

            sc.Parameters.Add("@DatabaseName", SqlDbType.VarChar, 50);
            sc.Parameters["@DatabaseName"].Value = databaseName;

            try
            {
                dbConn.Open();
                sc.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
            
        }

        /// <summary>
        /// Dataset of all data contained in configuration database.
        /// this data is used for:
        ///     a. Serverlist in grid
        ///     b. restore specific items as mirroring config.
        /// </summary>
        /// <returns></returns>
        public DataSet ServerList()
        {
            string spName = m_ConfigDatabaseSchemaName + ".usp_DRAPP_GetConfig"; ;
            DataSet ds = new DataSet("ServerList");
            ds.Locale = System.Globalization.CultureInfo.InvariantCulture;
            if (m_ConfigSvr == null)
            {
                throw new InvalidOperationException("Config Db Not Set");
            }

            string sqlConnStr = ConfigDataConnectionString();
           
            SqlConnection dbConn = new SqlConnection(sqlConnStr);
            SqlCommand sc = new SqlCommand(spName, dbConn);
            sc.CommandType = CommandType.StoredProcedure;

            if (UserInAdminGroup())
            {
                sc.Parameters.AddWithValue("@userIsAdmin", 0);
            }
            else
            {
                sc.Parameters.AddWithValue("@userIsAdmin", 1);
            }

            SqlDataAdapter da1 = new SqlDataAdapter(sc);

            try
            {
                da1.Fill(ds);
            }
            finally
            {
                if (dbConn.State == ConnectionState.Open)
                {
                    dbConn.Close();
                }
            }
            return ds;

        }
        /// <summary>
        /// Datatable for application, we are adding a column to the database data
        /// to show status of the applications restore objects.
        /// This is what gets returned to the callng classes.
        /// </summary>
        /// <param name="inTable"></param>
        /// <returns></returns>
        public DataTable RestoreList(DataTable inTable)
        {
            DataColumn restoreStatus = new DataColumn("Status");
            restoreStatus.DefaultValue = "Not Started";
            inTable.Columns.Add(restoreStatus);
            return inTable;
        }
        /// <summary>
        /// Gets a list of post DR Scripts to be run.
        /// </summary>
        /// <param name="DatabaseName"></param>
        /// <returns></returns>
        public DataTable GetPostActionScript(string databaseName)
        {
            DataSet ds = new DataSet("PostActions");
            ds.Locale = System.Globalization.CultureInfo.InvariantCulture;
            
            if (m_ConfigSvr == null)
            {
                throw new InvalidOperationException("Config Db Not Set");
            }

            string sqlConnStr = ConfigDataConnectionString();
            SqlConnection dbConn = new SqlConnection(sqlConnStr);
            string spName = m_ConfigDatabaseSchemaName + ".usp_DRAPP_GetPostActions";
            SqlCommand sqlCom = new SqlCommand(spName, dbConn);
            sqlCom.CommandType = CommandType.StoredProcedure;
            sqlCom.Parameters.Add("@DatabaseName", SqlDbType.VarChar, 50);
            sqlCom.Parameters["@DatabaseName"].Value = databaseName;
            SqlDataAdapter da1 = new SqlDataAdapter(sqlCom);

            try
            {
                da1.Fill(ds);
            }
            finally
            {
                if (dbConn.State == ConnectionState.Open)
                {
                    dbConn.Close();
                }
            }
            
            return ds.Tables[0];
        }

        /// <summary>
        /// Check to see if the config objects exist.
        /// </summary>
        /// <returns>bool</returns>
        public bool ConfigObjectsExist()
        {
            Server localServer = new Server(m_ConfigSvr);
            Database localDatabase = localServer.Databases[m_ConfigDB];
            if (localDatabase.Schemas[m_ConfigDatabaseSchemaName] == null)
            {
                return false;
            }
            if (localDatabase.Tables["AppConfig", m_ConfigDatabaseSchemaName] == null)
            {
                return false;
            }
            if (localDatabase.Tables["PostDrActions", m_ConfigDatabaseSchemaName] == null)
            {
                return false;
            }
            if (localDatabase.StoredProcedures["usp_DRAPP_GetConfig", m_ConfigDatabaseSchemaName] == null)
            {
                return false;
            }
            if (localDatabase.StoredProcedures["usp_DRAPP_GetPostActions", m_ConfigDatabaseSchemaName] == null)
            {
                return false;
            }

            return true;
        }

        /// <summary>
        /// Create database and all objects required by application.
        /// </summary>
        public void CreateDatabaseObjects()
        {
            Server localServer = new Server(m_ConfigSvr);
            Database localDatabase = localServer.Databases[m_ConfigDB];

            // Schema and Tables
            if (localDatabase.Schemas[m_ConfigDatabaseSchemaName] == null)
            {
                Schema DRAppSchema = new Schema(localDatabase, m_ConfigDatabaseSchemaName);
                DRAppSchema.Create();
            }
            if (localDatabase.Tables["AppConfig", m_ConfigDatabaseSchemaName] == null)
            {
                CreateAppConfigTable(localDatabase);
            }
            if (localDatabase.Tables["PostDrActions", m_ConfigDatabaseSchemaName] == null)
            {
                CreatePostActionsTable(localDatabase);
            }
            // Stored Procedures
            if (localDatabase.StoredProcedures["usp_DRAPP_GetConfig", m_ConfigDatabaseSchemaName] == null)
            {
                CreateGetConfigStoredProcedure(localDatabase);
            }
            if (localDatabase.StoredProcedures["usp_DRAPP_GetPostActions", m_ConfigDatabaseSchemaName] == null)
            {
                CreateGetPostActionsStoredProcedure(localDatabase);
            }

        }


        public void InsertConfigData(string principleServer, string mirrorServer, string databaseName, DrType drType)
        {

            string sqlConnStr = ConfigDataConnectionString();
            SqlConnection dbConn = new SqlConnection(sqlConnStr);
            string spName = m_ConfigDatabaseSchemaName + ".usp_insDrApp";
            SqlCommand sqlCom = new SqlCommand(spName, dbConn);
            sqlCom.CommandType = CommandType.StoredProcedure;
            
            sqlCom.Parameters.Add("@DbName", SqlDbType.VarChar, 50);
            sqlCom.Parameters["@DbName"].Value = databaseName;

            sqlCom.Parameters.Add("@principle", SqlDbType.VarChar, 20);
            sqlCom.Parameters["@principle"].Value = principleServer;

            sqlCom.Parameters.Add("@mirror", SqlDbType.VarChar, 20);
            sqlCom.Parameters["@mirror"].Value = mirrorServer;

            sqlCom.Parameters.Add("@drType", SqlDbType.Int);
            sqlCom.Parameters["@drType"].Value = drType;

            dbConn.Open();
            sqlCom.ExecuteNonQuery();

        }

        #endregion

        private string ConfigDataConnectionString()
        {
            return string.Format(@"Server={0}; Failover Partner={2}; trusted_connection=true; Database={1}; connection timeout=90", m_ConfigSvr, m_ConfigDB, m_ConfigMirrorServerSchemaName);
        }


        private bool UserInAdminGroup()
        {
            bool UserIsAdmin = true;
            string[] GroupsFromConfig = AdminGroup.Split(',');
            //if (GroupsFromConfig.Length > 1)
            //{
                //foreach (string s in GroupsFromConfig)
                //{
                //    if (UserInRole(s.Trim())) { return true; }
                //}
            //}
            return UserIsAdmin;
        }

        private bool UserInRole(string role)
        {
            bool test;
            WindowsPrincipal wp = new WindowsPrincipal(WindowsIdentity.GetCurrent());
            test = wp.IsInRole(role);
            return test;
        }

        /// <summary>
        /// Method to create the postActionsTable.
        /// </summary>
        /// <param name="LocalDatabase"></param>
        private void CreatePostActionsTable(Database localDatabase)
        {
            Table postDrActions = new Table(localDatabase, "PostDrActions", m_ConfigDatabaseSchemaName);
            // create DRAppId
            Column DRAppId = new Column(postDrActions, "DRAppId");
            DRAppId.DataType = DataType.Int;
            DRAppId.Nullable = false;
            postDrActions.Columns.Add(DRAppId);
            // create DRAppId
            Column PostActionId = new Column(postDrActions, "PostActionId");
            PostActionId.DataType = DataType.Int;
            PostActionId.Identity = true;
            PostActionId.Nullable = false;
            postDrActions.Columns.Add(PostActionId);
            // create ScriptAction
            Column ScriptAction = new Column(postDrActions, "ScriptAction");
            ScriptAction.DataType = DataType.VarCharMax;
            ScriptAction.Nullable = false;
            postDrActions.Columns.Add(ScriptAction);
            postDrActions.Create();
            // Console.WriteLine("PostDrActions table created");
            ForeignKey key = new ForeignKey(postDrActions, "fk_PostAction_Appid");
            ForeignKeyColumn fkc = new ForeignKeyColumn(key, "DRAppId", "DrAppId");
            key.ReferencedTable = "AppConfig";
            key.ReferencedTableSchema = m_ConfigDatabaseSchemaName;
            key.Columns.Add(fkc);
            key.Create();

        }

        /// <summary>
        /// Method to create the AppConfigTable
        /// </summary>
        /// <param name="LocalDatabase"></param>
        private void CreateAppConfigTable(Database localDatabase)
        {
            Table DRAppConfigTable = new Table(localDatabase, "AppConfig", m_ConfigDatabaseSchemaName);
            // Add DrAppId
            Column appid = new Column(DRAppConfigTable, "DrAppId");
            appid.DataType = DataType.Int;
            appid.Identity = true;
            appid.Nullable = false;
            DRAppConfigTable.Columns.Add(appid);

            // Add DatabaseName
            Column DatabaseName = new Column(DRAppConfigTable, "DatabaseName");
            DatabaseName.DataType = DataType.VarChar(50);
            DatabaseName.Nullable = false;
            DRAppConfigTable.Columns.Add(DatabaseName);

            // Add PdcServer
            Column PdcServer = new Column(DRAppConfigTable, "PdcServer");
            PdcServer.DataType = DataType.VarChar(50);
            PdcServer.Nullable = false;
            DRAppConfigTable.Columns.Add(PdcServer);

            // Add DrServer
            Column DrServer = new Column(DRAppConfigTable, "DrServer");
            DrServer.DataType = DataType.VarChar(50);
            DrServer.Nullable = false;
            DRAppConfigTable.Columns.Add(DrServer);

            // Add DrType
            Column DrType = new Column(DRAppConfigTable, "DrType");
            DrType.DataType = DataType.Int;
            DrType.Nullable = false;
            DRAppConfigTable.Columns.Add(DrType);

            // Create a PK Index for the table
            Index index = new Index(DRAppConfigTable, "PK_AppConfig_Appid");
            index.IndexKeyType = IndexKeyType.DriPrimaryKey;

            // The PK index will consist of 1 column, "ID"
            index.IndexedColumns.Add(new IndexedColumn(index, "DrAppId"));

            // Add the new index to the table.
            DRAppConfigTable.Indexes.Add(index);
            DRAppConfigTable.Create();
        }

        /// <summary>
        /// Create usp_DRAPP_GetConfig stored procedure.
        /// </summary>
        /// <param name="LocalDatabase"></param>
        private void CreateGetConfigStoredProcedure(Database localDatabase)
        {
            StoredProcedure spGetConfig = new StoredProcedure(localDatabase, "usp_DRAPP_GetConfig", m_ConfigDatabaseSchemaName);
            spGetConfig.TextMode = false;
            spGetConfig.TextBody = 
@"SELECT  DatabaseName, PdcServer, DrServer, CASE DrType
WHEN 0 THEN 'DbMirrored' ELSE 'OTHER' end AS DrType
FROM DRAPP.APPCONFIG
ORDER BY PdcServer";
            spGetConfig.Create();
        }

        /// <summary>
        /// Create usp_DRAPP_GetPostActions stored procedure.
        /// </summary>
        /// <param name="LocalDatabase"></param>
        private void CreateGetPostActionsStoredProcedure(Database localDatabase)
        {
            StoredProcedure spGetPostActions = new StoredProcedure(localDatabase, "usp_DRAPP_GetPostActions", m_ConfigDatabaseSchemaName);
            spGetPostActions.TextMode = false;
            StoredProcedureParameter dbName = new StoredProcedureParameter(spGetPostActions, "@DatabaseName", DataType.VarChar(50));
            spGetPostActions.Parameters.Add(dbName);

            spGetPostActions.TextBody =
@"select 
	ac.DatabaseName,
	pa.ScriptAction
from DRAPP.PostDrActions pa
join DRAPP.APPCONFIG AC
ON (ac.DRAppId = pa.DRAppId)
WHERE ac.DatabaseName = @DatabaseName;";
            spGetPostActions.Create();
        }
    }
}
